package com.iaiai.cobra.repository.config;

import com.iaiai.cobra.repository.constants.ConfigKey;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.init.DataSourceInitializer;
import org.springframework.jdbc.datasource.init.DatabasePopulator;
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import org.springframework.util.ResourceUtils;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.Timer;
import java.util.TimerTask;

/**
 * Created with IntelliJ IDEA.
 * Package: com.txt.bamboo.repository.config
 * Author: iaiai
 * Create Time: 2021/2/16 8:56
 * QQ: 176291935
 * Url: http://iaiai.iteye.com
 * Email: 176291935@qq.com
 * Description:
 */
@Slf4j
@Configuration
public class CustomizeDataSourceInitializer {

    @Bean
    public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
        log.info("=== db is init ===");

        final DataSourceInitializer initializer = new DataSourceInitializer();
        // 设置数据源
        initializer.setDataSource(dataSource);

        try {
            int version = getDBVersion(dataSource);
            log.info("db is version : {}", version);

            Resource[] resources = new PathMatchingResourcePatternResolver().getResources(ResourceUtils.CLASSPATH_URL_PREFIX + "sql/*.sql");
            resources = sortResources(resources);   //排序
            DatabasePopulator databasePopulator = databasePopulator(resources,version);
            initializer.setDatabasePopulator(databasePopulator);

            log.info("=========================");
            String filename = resources[resources.length-1].getFilename();
            int v = Integer.parseInt(filename.substring(0,filename.indexOf("_")));
            if(v>version){
                //需要更新版本，延迟一秒后执行
                new Timer().schedule(new TimerTask() {
                    @SneakyThrows
                    public void run() {
                        updateVersion(dataSource,v);
                        log.info("db version update is {}",v);
                    }
                }, 1000);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return initializer;
    }

    //更新版本
    private void updateVersion(DataSource dataSource,int version) throws SQLException {
        Connection connection = connection = dataSource.getConnection();
        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement("update config set value=? where `key`=?");
            preparedStatement.setInt(1,version);
            preparedStatement.setString(2, ConfigKey.dbVersion.getKey());
            preparedStatement.execute();

            DbUpgradeVersion upgradeVersion = new DbUpgradeVersion();
            upgradeVersion.handleVersion(connection,preparedStatement,version);
        }finally {
            try {
                if (preparedStatement != null) preparedStatement.close();
            } catch (Exception e) {}
        }

        try {
            if(connection!=null)connection.close();
        }catch (Exception e){}
    }

    //排序，按版本号来排序
    private Resource[] sortResources(Resource[] resources){
        for (int i = 0; i < resources.length - 1; i++) {
            for (int j = 0; j < resources.length - i - 1; j++) {
                int version1 = Integer.parseInt(resources[j].getFilename().substring(0,resources[j].getFilename().indexOf("_")));
                int version2 = Integer.parseInt(resources[j+1].getFilename().substring(0,resources[j+1].getFilename().indexOf("_")));
                if (version1 > version2) {
                    // 交换ns[j]和ns[j+1]:
                    Resource tmp = resources[j];
                    resources[j] = resources[j+1];
                    resources[j+1] = tmp;
                }
            }
        }

        return resources;
    }

    private DatabasePopulator databasePopulator(Resource[] resources,int version) throws IOException {
        final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        for(Resource resource : resources) {
            int v = Integer.parseInt(resource.getFilename().substring(0,resource.getFilename().indexOf("_")));
            if(v>version) {
                log.info("sql : {}", resource.getURL().getPath());
                populator.addScripts(resource);
            }
        }
        populator.setSeparator(";");    //每条语句的分隔符，可不配置，默认找";"如果找不到则按\n来分割
        return populator;
    }

    //读取当前版本号，做数据库初始化
    private int getDBVersion(DataSource dataSource) throws SQLException {
        int version = 0;
        Connection connection = connection = dataSource.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        boolean bol = false;    //是否有表

        try {
            DatabaseMetaData metaData = connection.getMetaData();
            resultSet = metaData.getTables(connection.getCatalog(), null, "config", new String[]{"TABLE"});

            bol = resultSet.next();
        } finally {
            try {
                if (resultSet != null) resultSet.close();
            } catch (Exception e) {}
        }

        if(bol) {
            try {
                preparedStatement = connection.prepareStatement("select `value` from config where `key`=?");
                preparedStatement.setString(1, ConfigKey.dbVersion.getKey());
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    version = Integer.parseInt(resultSet.getString("value"));
                }
            }finally{
                try {
                    if (resultSet != null) resultSet.close();
                } catch (Exception e) {}

                try {
                    if (preparedStatement != null) preparedStatement.close();
                } catch (Exception e) {}
            }
        }


        try {
            if(connection!=null)connection.close();
        }catch (Exception e){}

        return version;
    }

}
